package com.demo.controller;

import com.alibaba.excel.support.ExcelTypeEnum;
import com.demo.common.ResponseData;
import com.demo.pojo.User;
import com.demo.pojo.UserExcel;
import com.demo.service.UserService;
import com.demo.util.ExcelUtil;
import com.google.common.collect.Lists;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping(value = "/excel")
@Slf4j
public class ExcelController {

    @Autowired
    UserService userService;

    /**
     * 下载Excel模板
     */
    @GetMapping("/template")
    public void downloadTemplate(HttpServletResponse response) {
        String fileName = "导入用户模板";
        String sheetName = "导入用户模板";
        List<UserExcel> userList = new ArrayList<>();
        //userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
        //userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
        try {
            ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载Excel模板
     */
    @GetMapping("/getexcel")
    public void getexcel(HttpServletResponse response) {
        String fileName = "导入用户模板";
        String sheetName = "导入用户模板";
        List<UserExcel> userList = new ArrayList<>();
        //userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
        //userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
        try {
            ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    /**
     * 批量处理上传文件
     *
     * @param file
     * @return
     */
    @ApiOperation(value = "上传文件批量处理", notes = "上传文件批量处理")
    @PostMapping("/batch/upload")
    public void batchUpload(MultipartFile file) {
        log.info("[项目配置管理]上传项目权限开始");
        try {
            if (null == file) {
                log.error("0", "上传文件不能为空");
            }
            // 获取上传文件
             userService.upload(file.getInputStream());

        } catch (Exception e) {
            log.error("[项目配置管理]上传项目权限异常:", e);
         }
        // 返回结果
        log.info("上传项目权限结束");

    }






    public static void main(String[] args) throws IOException {
        exportExcel();
    }
    /**
     *  导出excel （HSSFWorkbook）
     */

    public static void exportExcel() throws IOException {

        /** 第一步，创建一个Workbook，对应一个Excel文件  */
        HSSFWorkbook wb = new HSSFWorkbook();

        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        HSSFSheet sheet = wb.createSheet("excel导出标题");

        /** 第三步，设置样式以及字体样式*/
        HSSFCellStyle titleStyle = createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = createContentCellStyle(wb);

        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        //HSSFRow row0 = sheet.createRow(rowNum++);
        //row0.setHeight((short) 800);// 设置行高

//        String title = "excel导出标题";
//        HSSFCell c00 = row0.createCell(0);
//        c00.setCellValue(title);
//        c00.setCellStyle(titleStyle);


//        Row row1 = sheet.createRow(rowNum++);
//        row1.createCell(0).setCellValue("上年末Balance");
//        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
//        row1.createCell(1).setCellValue("Movement");
//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 5));
//
//        row1.createCell(6).setCellValue("备注");
//        sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 7));
//
//        row1.createCell(8).setCellValue("备注2");
//        sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 10));
//
//        Row row2 = sheet.createRow(rowNum++);
//        row2.createCell(1).setCellValue("Balance");
//        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3));
//
//        row2.createCell(4).setCellValue("合并单元格");
//        sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));

        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        //sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//标题合并单元格操作，6为总列数

        // 第二行
//        HSSFRow row1 = sheet.createRow(rowNum++);
//        row1.setHeight((short) 500);
//        String[] row_first = {"填表单位：", "", "", "", "", " 2019年第2季度 ", ""};
//        for (int i = 0; i < row_first.length; i++) {
//            HSSFCell tempCell = row1.createCell(i);
//            tempCell.setCellStyle(headerStyle);
//            if (i == 0) {
//                tempCell.setCellValue(row_first[i] + "测试单位");
//            } else if (i == 5) {
//                tempCell.setCellStyle(headerStyle);
//                tempCell.setCellValue(row_first[i]);
//            } else {
//                tempCell.setCellValue(row_first[i]);
//            }
//        }

        // 合并
        //sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
        //sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));

        //第三行
        //HSSFRow row2 = sheet.createRow(rowNum++);
        //row2.setHeight((short) 700);
//        String[] row_second = {"名称", "采集情况", "", "备注", "备注"};
//        for (int i = 0; i < row_second.length; i++) {
//            HSSFCell tempCell = row2.createCell(i);
//            tempCell.setCellValue(row_second[i]);
//            tempCell.setCellStyle(headerStyle);
//        }

/*

        row2.createCell(0).setCellValue("名称");
        row2.createCell(1).setCellValue("采集情况");
        row2.createCell(6).setCellValue("备注");

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));//名称
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 5));//人数情况
        //sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));//登记情况
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));//备注

        //第三行
        HSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 700);
        String[] row_third = {"", "登记数(人)",  "办证率(%)", "", "签订数(份)", "",""};
        for (int i = 0; i < row_third.length; i++) {
            HSSFCell tempCell = row3.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(headerStyle);
        }

        sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 2));//备注
*/





        Row row1 = sheet.createRow(rowNum++);
        row1.createCell(0).setCellValue("上年末Balance");
        Row row;
        List  list = Lists.newArrayList();
        list.add("1");
        list.add("2");
        list.add("3");
        list.add("4");

       int headRow =0;
        for (int i = 2; i <= 20; i++) {

            Cell cell = sheet.getRow(headRow).createCell(i);
            cell.setCellValue("1");

            cell.setCellValue("2");
            cell.setCellValue("3");
            cell.setCellValue("4");
            if (i == 7){
                cell.setCellValue("1");

                cell.setCellValue("2");
                cell.setCellValue("3");
                cell.setCellValue("4");
            }

        }
        Row row2 = sheet.createRow(rowNum+1);
        row2.createCell(0).setCellValue("上年末Balance");

         //Cell cell = sheet.getRow(headRow+1).createCell(0);
        //cell.setCellValue("888");
            //导出
        //HttpServletResponse response = this.getResponse();
        String fileName = "报表名称.xls";
        String filePath="/Users/liangzai/Desktop/sample.xls";//文件路径
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filePath);
            wb.write(out);//保存Excel文件
            out.close();//关闭文件流
            System.out.println("OK!");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }



    }



    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色

        HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }




    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }




    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    private static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        HSSFFont font = wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }
}
